﻿CREATE PROCEDURE CAT.GetCustomers
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT,
		-- The result message of the procedure. If all goes ok it will be empty, else it will contain the message
		-- of the error that has been thrown.
		@resultMessage NVARCHAR(100)


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'


	-----------------------
	-- Begin Query
	-----------------------
	IF (@result = 0)
	BEGIN
		BEGIN TRY
			SELECT * FROM CAT.Customers ORDER BY Code ASC
		END TRY
		BEGIN CATCH
			SELECT
				@result = ERROR_NUMBER(),
				@resultMessage = ERROR_MESSAGE()
		END CATCH
	END


	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
		PRINT @finishEndMSG
	ELSE 
		PRINT @finishEndMSG + ' Result: ' + CAST(@result AS VARCHAR) + ', message: ' + @resultMessage

	RETURN @result
END

GO
GRANT EXECUTE
    ON OBJECT::CAT.GetCustomers TO [SystemPublic]
    AS [dbo];